Go to the documentation of this file.
00001 using System;
00002 using System.Collections.Generic;
00003 using System.Linq;
00004 using System.Text;
00005 using System.Data;
00006 using System.Data.SqlClient;
00007 namespace FillCustomQueryTableCL
00008 {
00009     public class ConsistencyCheckQuery:Query
00010     {
00011         private static string QueryTypeName = "Consistency Check";
00012         private readonly string GetParametersQuery =
00013                             "SELECT [Query Name] [Name], null TypeName, 0 [order] " +
00014                             "FROM [Consistency Check] " +
00015                             "WHERE [Query Name] like '{0}(%' " +
00016                             "UNION " +
00017                             "SELECT  " +
00018                             "    sys.parameters.name [Name], " +
00019                             "    sys.types.name TypeName, " +
00020                             "    sys.parameters.parameter_id " +
00021                             "FROM sys.parameters " +
00022                             "    LEFT JOIN sys.objects on sys.parameters.object_id=sys.objects.object_id " +
00023                             "    LEFT JOIN sys.types ON sys.parameters.user_type_id=sys.types.user_type_id " +
00024                             "WHERE sys.objects.name='{0}' " +
00025                             "ORDER by [order] ";
00026 
00027         public ConsistencyCheckQuery(string name)
00028             : base(name, QueryTypeName)
00029         {
00030             LoadParameters(GetParametersQuery);
00031         }
00032 
00033         protected ConsistencyCheckQuery(string name, string queryTypeName, string getParametersQuery)
00034             : base(name, queryTypeName)
00035         {
00036             LoadParameters(getParametersQuery);
00037         }
00038 
00039         private void LoadParameters(string getParametersQuery)
00040         {
00041             using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
00042             {
00043                 sqlCommand.CommandText = string.Format(getParametersQuery, Name); ;
00044                 using (SqlDataReader reader = sqlCommand.ExecuteReader())
00045                 {
00046                     string[] param;
00047                     if (reader.Read() && reader["TypeName"] == DBNull.Value)
00048                     {
00049                         string queryPar = (string)reader["Name"];
00050                         int index = queryPar.IndexOf('(');
00051                         queryPar = queryPar.Trim().Substring(index + 1, queryPar.Length - index - 2);
00052                         param = queryPar.Split(',');
00053                         reader.Read();
00054                         int count = queryPar.Trim() == "" ? 0 : param.Length;
00055                         for (int i = 0; i < count; i++)
00056                         {
00057 
00058                             string typeName;
00059                             if(param[i].Trim() == "User")
00060                                 typeName = "UserID";
00061                             else if(param[i].Trim() == "PK")
00062                                 typeName = "PrimaryKey";
00063                             else
00064                                 typeName = GetTypeFromSQLType((string)reader["TypeName"]);
00065 
00066                             Parameters.Add(new Parameter(Name, ((string)reader["Name"]).Substring(1), param[i].Trim(),
00067                                                             typeName, -1, null, i + 1));
00068                             reader.Read();
00069                         }
00070                     }
00071                     else
00072                     {
00073                         if (reader.HasRows)
00074                         {
00075                             do
00076                             {
00077                                 Parameters.Add(new Parameter(Name, ((string)reader["Name"]).Substring(1), ((string)reader["Name"]).Substring(1),
00078                                                                 GetTypeFromSQLType((string)reader["TypeName"]), -1, null, (int)reader["Order"]));
00079                             }
00080                             while (reader.Read());
00081                         }
00082                     }
00083                 }
00084             }
00085         }
00086         protected override void PreСorrectParameters(System.Data.SqlClient.SqlDataReader reader)
00087         {
00088 
00089         }
00090         protected override void СorrectParameters()
00091         {
00092 
00093         }
00094     }
00095 }